﻿using DataObjects;
using InfrastructureSqlServer.Helpers;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DataAccess
{
    public class FilialDAO
    {
        private List<Filial> CarregarListaFilial(SqlDataReader dr)
        {
            List<Filial> liFilial = new List<Filial>();

            while (dr.Read())
            {
                Filial filial = new Filial();
                filial.FilialId = int.Parse(dr["FILIALID"].ToString());
                filial.Ativo = int.Parse(dr["ATIVO"].ToString());
                filial.Descricao = dr["DESCRICAO"].ToString();
                filial.Endereco = dr["ENDERECO"].ToString();
                filial.EmpresaId = int.Parse(dr["EMPRESAID"].ToString());
                EmpresaDAO _empresaDAO = new EmpresaDAO();
                filial.Empresa = _empresaDAO.Consultar(filial.EmpresaId)[0];

                liFilial.Add(filial);
            }

            return liFilial;
        }

        public void Inserir(Filial _filial)
        {
            SqlParameter[] paramsToSP = new SqlParameter[4];

            paramsToSP[0] = new SqlParameter("@ativo", _filial.Ativo);
            paramsToSP[1] = new SqlParameter("@descricao", _filial.Descricao);
            paramsToSP[2] = new SqlParameter("@endereco", _filial.Endereco);
            paramsToSP[3] = new SqlParameter("@empresaId", _filial.EmpresaId);

            try
            {
                SqlHelper.ExecuteNonQuery(ConfigurationManager.ConnectionStrings["conexao"].ToString(),
                                            CommandType.StoredProcedure, "stp_insert_filial", paramsToSP);

            }
            catch (Exception e)
            {
                throw new Exception("Erro ao inserir filial, Erro: " + e.Message);
            }
            
        }

        public bool Alterar(Filial _filial)
        {
            SqlParameter[] paramsToSP = new SqlParameter[5];

            paramsToSP[0] = new SqlParameter("@filialid", _filial.FilialId);
            paramsToSP[1] = new SqlParameter("@ativo", _filial.Ativo);
            paramsToSP[2] = new SqlParameter("@descricao", _filial.Descricao);
            paramsToSP[3] = new SqlParameter("@endereco", _filial.Endereco);
            paramsToSP[4] = new SqlParameter("@empresaId", _filial.EmpresaId);

            try
            {
                SqlHelper.ExecuteNonQuery(ConfigurationManager.ConnectionStrings["conexao"].ToString(),
                                            CommandType.StoredProcedure, "stp_update_filial", paramsToSP);
                return true;
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao alterar filial, Erro: " + e.Message);
            }

        }

        public bool Deletar(int _filialID)
        {
            SqlParameter[] paramsToSP = new SqlParameter[1];

            paramsToSP[0] = new SqlParameter("@filialId", _filialID);

            try
            {
                SqlHelper.ExecuteNonQuery(ConfigurationManager.ConnectionStrings["conexao"].ToString(),
                                            CommandType.StoredProcedure, "stp_delete_filial", paramsToSP);
                return true;
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao deletar filial, Erro: " + e.Message);
            }

        }

        public List<Filial> Consultar(int _filialId)
        {
            string sqlQuery = string.Format("SELECT * FROM FILIAL WHERE FILIALID = {0}", _filialId);

            try
            {
                return CarregarListaFilial(SqlHelper.ExecuteReader(ConfigurationManager.ConnectionStrings["conexao"].ToString(),
                                            CommandType.Text, sqlQuery));
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao consultar filial, Erro: " + e.Message);
            }

        }

        public List<Filial> Consultar(string _filial)
        {
            string sqlQuery = string.Format("SELECT * FROM FILIAL WHERE descricao like '%{0}%'", _filial);

            try
            {
                return CarregarListaFilial(SqlHelper.ExecuteReader(ConfigurationManager.ConnectionStrings["conexao"].ToString(),
                                            CommandType.Text, sqlQuery));
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao consultar filial, Erro: " + e.Message);
            }

        }

        public List<Filial> Consultar()
        {
            string sqlQuery = string.Format("SELECT * FROM FILIAL ");

            try
            {
                return CarregarListaFilial(SqlHelper.ExecuteReader(ConfigurationManager.ConnectionStrings["conexao"].ToString(),
                                            CommandType.Text, sqlQuery));
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao consultar filial, Erro: " + e.Message);
            }

        }

    }//class
}//namespace
